﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;



namespace festivalDatabase
{
    public partial class editVendors : userInterface
    {
        int oldLicNum;

        public editVendors(dbAccessor db, festivalTypes type, string currentFestivalString)
        {
            InitializeComponent();
            setIcon();
            currentFestivalType = type;

            vendorCreditCardTypeDropdown.Items.Add("VISA");
            vendorCreditCardTypeDropdown.Items.Add("MSTR");
            vendorCreditCardTypeDropdown.Items.Add("DSVR");
            vendorCreditCardTypeDropdown.Items.Add("AMEX");

            if (type.ToString() == "Music")
            {
                categoryDropdown.Items.Add("1 - Food/beverage");
                categoryDropdown.Items.Add("2 - Beverage");
                categoryDropdown.Items.Add("3 - Crafts");
            }
            if (type.ToString() == "Art")
            {
                categoryDropdown.Items.Add("1 - Food/beverage");
                categoryDropdown.Items.Add("2 - Beverage");
            }
            
            
            dbAccess = db;
            updateVendorList();
            addVendorRadioButton.Checked = true;
        }
        /// <summary>
        /// Refreshes the ListBox so that it reflects the values in the database.
        /// </summary>
        public void updateVendorList()
        {
            vendorListBox.Items.Clear();

            SQLiteCommand query = dbAccess.getCommand();

            query.CommandText = "SELECT vendor_name, license_number " +
                                "FROM vendor " +
                                "JOIN festival_has_vendor " +
                                "ON vendor.license_number=festival_has_vendor.vendor_license_num " +
                                "JOIN festival " +
                                "ON festival.id=festival_has_vendor.festival_id " +
                                "WHERE festival_type=@type;";
            SQLiteParameter param = new SQLiteParameter("@type", currentFestivalType.ToString());
            query.Parameters.Add(param);

            try
            {
                DataTable dt = dbAccess.executeQuery(query);

                foreach (DataRow row in dt.Rows)
                {
                    vendorListBox.Items.Add((row["vendor_name"].ToString() + ", " + row["license_number"].ToString()));
                }
            }
            catch
            {
                vendorListBox.Items.Add("Error loading festivals");
            }
        }
        /// <summary>
        /// Runs updateVendorList() whenever the vendorListBox is clicked.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void vendorListBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (vendorListBox.SelectedIndex >= 0)
            {
                oldLicNum = getLicNum(vendorListBox.SelectedItem.ToString());
                updateVendorInfo(vendorListBox.SelectedIndex);
                festivalComboBox.Enabled = true;
                updateFestivalListAndComboBox();
            }
        }

        private void updateFestivalListAndComboBox()
        {
            festivalAttendingListBox.Items.Clear();
            festivalComboBox.Items.Clear();

            SQLiteCommand query = dbAccess.getCommand();
            query.CommandText = "SELECT name, id " +
                                "FROM festival JOIN festival_has_vendor ON festival.id=festival_has_vendor.festival_id " +
                                "WHERE vendor_license_num=@licNum;";
            SQLiteParameter param = new SQLiteParameter("@licNum", oldLicNum);
            query.Parameters.Add(param);

            DataTable dt = dbAccess.executeQuery(query);

            try
            {
                foreach (DataRow row in dt.Rows)
                {
                    festivalAttendingListBox.Items.Add(row["name"].ToString() + ", " + row["id"].ToString());
                }
            }
            catch
            {
                // do nothing
            }

            query = dbAccess.getCommand();
            query.CommandText = "SELECT name, id FROM festival WHERE festival_type=@type " +
                                "EXCEPT SELECT name, id " +
                                "FROM festival JOIN festival_has_vendor ON festival.id=festival_has_vendor.festival_id " +
                                "WHERE vendor_license_num=@licNum;";
            param = new SQLiteParameter("@type", currentFestivalType.ToString());
            query.Parameters.Add(param);
            param = new SQLiteParameter("@licNum", oldLicNum);
            query.Parameters.Add(param);

            dt = dbAccess.executeQuery(query);

            try
            {
                foreach (DataRow row in dt.Rows)
                {
                    festivalComboBox.Items.Add(row["name"].ToString() + ", " + row["id"].ToString());
                }
            }
            catch
            {
                // do nothing
            }
        }

        /// <summary>
        /// Checks to make sure the input values are valid and updates them, inserting them into the database.
        /// </summary>
        /// <param name="selectedIndex">The index of the item selected in the ListBox.</param>
        public void updateVendorInfo(int selectedIndex)
        {
            SQLiteCommand query = dbAccess.getCommand();

            query.CommandText = "SELECT cctype, credit_card_number, license_number, category, vendor_name, telephone_number " +
                                "FROM vendor " +
                                "WHERE license_number=@licNum;";
            SQLiteParameter param = new SQLiteParameter("@licNum", getLicNum(vendorListBox.SelectedItem.ToString()));
            query.Parameters.Add(param);

            try
            {
                festivals = dbAccess.executeQuery(query);
                {
                    DataRow row = festivals.Rows[0];
                    vendorCreditCardTypeDropdown.Text = (row["cctype"].ToString());
                    vendorCreditCardNumberTextBox.Text = (row["credit_card_number"].ToString());
                    vendorLicenseNumberTextBox.Text = (row["license_number"].ToString());

                    if (row["category"].ToString() == "1")
                    {
                        categoryDropdown.Text = "1 - Food/beverage";
                    }
                    if (row["category"].ToString() == "2")
                    {
                        categoryDropdown.Text = "2 - Beverage";
                    }
                    if (row["category"].ToString() == "3")
                    {
                        categoryDropdown.Text = "3 - Crafts";
                    }
                    vendorNameTextBox.Text = (row["vendor_name"].ToString());
                    vendorTelephoneNumberTextBox.Text = (row["telephone_number"].ToString());
                }
            }
            catch
            {
                vendorListBox.Items.Add("Error loading vendor info");
            }
        }

        /// <summary>
        /// Adds the attributes in the textboxes to the database when the add button is clicked.
        /// </summary>
        private void addVendor()
        {
            if (validNumLength(vendorTelephoneNumberTextBox.Text, 10, true))
            {
                if (validNumLength(vendorLicenseNumberTextBox.Text, 7, false))
                {
                    if (validNumLength(vendorCreditCardNumberTextBox.Text, 9, true))
                    {
                        if ((categoryDropdown.Text == "1 - Food/beverage"
                            || categoryDropdown.Text == "2 - Beverage"
                            || categoryDropdown.Text == "3 - Crafts"
                            || categoryDropdown.Text == ""))
                        {

                            //EDITING THE VENDOR TABLE
                            SQLiteCommand nonQuery = dbAccess.getCommand();
                            nonQuery.CommandText = "INSERT INTO vendor (license_number";

                            if (vendorNameTextBox.Text.Length > 0)
                                nonQuery.CommandText += ", vendor_name";
                            if (validNumLength(vendorCreditCardNumberTextBox.Text.ToString(), 9, false))
                                nonQuery.CommandText += ", credit_card_number";
                            if (vendorCreditCardTypeDropdown.SelectedIndex >= 0)
                                nonQuery.CommandText += ", cctype";
                            if (categoryDropdown.SelectedIndex >= 0)
                                nonQuery.CommandText += ", category";
                            if (validNumLength(vendorTelephoneNumberTextBox.Text.ToString(), 10, false))
                                nonQuery.CommandText += ", telephone_number";

                            nonQuery.CommandText += ") VALUES (@licNum";
                            SQLiteParameter licNum = new SQLiteParameter("@licNum", getLicNum());
                            nonQuery.Parameters.Add(licNum);

                            if (vendorNameTextBox.Text.Length > 0)
                            {
                                nonQuery.CommandText += ", @name";
                                SQLiteParameter param = new SQLiteParameter("@name", vendorNameTextBox.Text.ToString());
                                nonQuery.Parameters.Add(param);
                            }
                            if (validNumLength(vendorCreditCardNumberTextBox.Text.ToString(), 9, false))
                            {
                                nonQuery.CommandText += ", @ccNum";
                                SQLiteParameter param = new SQLiteParameter("@ccNum", Int32.Parse(vendorCreditCardNumberTextBox.Text.ToString()));
                                nonQuery.Parameters.Add(param);
                            }
                            if (vendorCreditCardTypeDropdown.SelectedIndex >= 0)
                            {
                                nonQuery.CommandText += ", @ccType";
                                SQLiteParameter param = new SQLiteParameter("@ccType", vendorCreditCardTypeDropdown.SelectedItem.ToString());
                                nonQuery.Parameters.Add(param);
                            }
                            if (categoryDropdown.SelectedIndex >= 0)
                            {
                                nonQuery.CommandText += ", @cat";
                                SQLiteParameter param = new SQLiteParameter("@cat", categoryDropdown.SelectedIndex + 1);
                                nonQuery.Parameters.Add(param);
                            }
                            if (validNumLength(vendorTelephoneNumberTextBox.Text.ToString(), 10, false))
                            {
                                nonQuery.CommandText += ", @phone";
                                SQLiteParameter param = new SQLiteParameter("@phone", vendorTelephoneNumberTextBox.Text.ToString());
                                nonQuery.Parameters.Add(param);
                            }

                            nonQuery.CommandText += ");";

                            dbAccess.executeNonQuery(nonQuery);
                            MessageBox.Show("Vendor added successfully.");

                            vendorNameTextBox.Text = "";
                            vendorCreditCardNumberTextBox.Clear();
                            vendorLicenseNumberTextBox.Clear();
                            vendorTelephoneNumberTextBox.Clear();
                            vendorCreditCardTypeDropdown.Text = "";
                            categoryDropdown.Text = "";
                            updateVendorList();
                        }
                        else
                        {
                            MessageBox.Show("Please choose a category from the dropdown box.");
                        }
                    }
                    else
                    {
                        MessageBox.Show("Credit card number must be 9 digits long.");
                    }
                }
                else
                {
                    MessageBox.Show("Vendor must have a 7-digit license number.");
                }
            }
            else
            {
                MessageBox.Show("Telephone Number must be 10 digits long.");
            }
        }

        /// <summary>
        /// Returns the license number in the masked text box in int format.
        /// </summary>
        /// <returns></returns>
        private int getLicNum()
        {
            char[] num = vendorLicenseNumberTextBox.Text.ToCharArray();
            char[] realNum = new char[7];
            int index = 0;

            for (int i = 0; i < num.Length; i++)
            {
                if (num[i] == '0' || num[i] == '1' || num[i] == '2' || num[i] == '3' || num[i] == '4' || num[i] == '5' || num[i] == '6' || num[i] == '7' || num[i] == '8' || num[i] == '9')
                    realNum[index++] = num[i];
            }

            return Int32.Parse(new string(realNum));
        }

        /// <summary>
        /// Allows the user to select an item in the ListBox, change its information and click
        /// the update button to update the information in the database.
        /// </summary>
        private void editVendorInfo()
        {
            if (validNumLength(vendorLicenseNumberTextBox.Text, 7, false) && licNumIsUnique())
            {
                if (validNumLength(vendorCreditCardNumberTextBox.Text, 9, true))
                {
                    if ((categoryDropdown.Text == "1 - Food/beverage" 
                        || categoryDropdown.Text == "2 - Beverage"
                        || categoryDropdown.Text == "3 - Crafts"
                        || categoryDropdown.Text == ""))
                    {
                        SQLiteCommand nonQuery = dbAccess.getCommand();

                        nonQuery.CommandText = "UPDATE vendor SET ";
                        nonQuery.CommandText += "license_number=@lic_num";
                        nonQuery.Parameters.Add("@lic_num", DbType.Int32);
                        nonQuery.Parameters["@lic_num"].Value = getLicNum();
                                
                        if (vendorCreditCardTypeDropdown.SelectedIndex >= 0)
                        {
                            nonQuery.CommandText += ", cctype=@cctype";
                            nonQuery.Parameters.Add("@cctype", DbType.String);
                            nonQuery.Parameters["@cctype"].Value = vendorCreditCardTypeDropdown.Text.ToString();
                        }
                        if (validNumLength(vendorCreditCardNumberTextBox.Text.ToString(), 9, false))
                        {
                            nonQuery.CommandText += ", credit_card_number=@card_num";
                            nonQuery.Parameters.Add("@card_num", DbType.String);
                            nonQuery.Parameters["@card_num"].Value = vendorCreditCardNumberTextBox.Text.ToString();
                        }
                        if (categoryDropdown.SelectedIndex >= 0)
                        {
                            nonQuery.CommandText += ", category=@cat";
                            nonQuery.Parameters.Add("@cat", DbType.Int32);
                            int cat = categoryDropdown.SelectedIndex + 1;
                            nonQuery.Parameters["@cat"].Value = cat;
                        }
                        if (validNumLength(vendorTelephoneNumberTextBox.Text.ToString(), 10, true))
                        {
                            nonQuery.CommandText += ", telephone_number=@phone";
                            nonQuery.Parameters.Add("@phone", DbType.String);
                            nonQuery.Parameters["@phone"].Value = vendorTelephoneNumberTextBox.Text.ToString();
                        }
                        if (vendorNameTextBox.Text.Length > 0)
                        {
                            nonQuery.CommandText += ", vendor_name=@name";
                            nonQuery.Parameters.Add("@name", DbType.String);
                            nonQuery.Parameters["@name"].Value = vendorNameTextBox.Text.ToString();
                        }
                        nonQuery.CommandText += " WHERE license_number=@oldLicNum;";
                        SQLiteParameter param = new SQLiteParameter("@oldLicNum", oldLicNum);
                        nonQuery.Parameters.Add(param);

                        dbAccess.executeNonQuery(nonQuery);

                        nonQuery = dbAccess.getCommand();
                        nonQuery.CommandText = "UPDATE festival_has_vendor " +
                                               "SET vendor_license_num=@licNum " +
                                               "WHERE vendor_license_num=@oldLicNum;";
                        nonQuery.Parameters.Add(param);
                        param = new SQLiteParameter("@licNum", getLicNum());
                        nonQuery.Parameters.Add(param);
                        dbAccess.executeNonQuery(nonQuery);

                        updateVendorList();
                        vendorNameTextBox.Text = "";
                        vendorLicenseNumberTextBox.Text = "";
                        vendorCreditCardNumberTextBox.Text = "";
                        vendorTelephoneNumberTextBox.Text = "";
                        categoryDropdown.Text = "";
                        vendorCreditCardTypeDropdown.Text = "";
                    }
                    else
                    {
                        MessageBox.Show("Vendor must have category 1-3.");
                    }
                }
                else
                {
                    MessageBox.Show("Vendor must have 9-digit credit card.");
                }
            }
            else
            {
                MessageBox.Show("Vendor must have a unique 7-digit license number.");
            }
        }

        private bool licNumIsUnique()
        {
            SQLiteCommand query = dbAccess.getCommand();
            query.CommandText = "SELECT license_number " +
                                "FROM vendor;";

            DataTable dt = dbAccess.executeQuery(query);

            int newLicNum = getLicNum();
            foreach (DataRow row in dt.Rows)
            {
                int existingLicNum = getLicNum(row["license_number"].ToString());
                if (existingLicNum == newLicNum)
                    return false;
            }

            return true;
        }

        /// <summary>
        /// Deletes the selected item in the ListBox from the database.
        /// </summary>
        private void removeVendor()
        {
            if (vendorListBox.SelectedItem != null)
            {
                vendorNameTextBox.Text = vendorListBox.SelectedItem.ToString();
            }

            SQLiteCommand nonQuery = dbAccess.getCommand();
            nonQuery.CommandText = "DELETE " +
                                   "FROM vendor " +
                                   "WHERE license_number=@licNum;";
            SQLiteParameter param = new SQLiteParameter("@licNum", getLicNum());
            nonQuery.Parameters.Add(param);
            dbAccess.executeNonQuery(nonQuery);

            nonQuery = dbAccess.getCommand();
            nonQuery.CommandText = "DELETE FROM festival_has_vendor " +
                                   "WHERE license_number=@licNum;";
            nonQuery.Parameters.Add(param);
            dbAccess.executeNonQuery(nonQuery);

            updateVendorList();
            vendorNameTextBox.Text = "";
            vendorTelephoneNumberTextBox.Text = "";
            vendorLicenseNumberTextBox.Text = "";
            vendorCreditCardNumberTextBox.Text = "";
            categoryDropdown.Text = "";
            vendorCreditCardTypeDropdown.Text = "";
        }


        private int getLicNum(string nameAndLicNum)
        {
            return Int32.Parse(nameAndLicNum.Substring(nameAndLicNum.Length - 7, 7));
        }

        private void addVendorRadioButton_CheckedChanged(object sender, EventArgs e)
        {
            clearFields();
            enableFields(true);
            vendorListBox.Enabled = false;
            submitButton.Text = "Add Vendor";
        }

        private void editVendorRadioButton_CheckedChanged(object sender, EventArgs e)
        {
            clearFields();
            enableFields(true);
            vendorListBox.Enabled = true;
            submitButton.Text = "Edit Vendor";
        }

        private void deleteVendorRadioButton_CheckedChanged(object sender, EventArgs e)
        {
            clearFields();
            enableFields(false);
            vendorListBox.Enabled = true;
            submitButton.Text = "Delete Vendor";
        }

        private void clearFields()
        {
            vendorNameTextBox.Text = "";
            vendorTelephoneNumberTextBox.Clear();
            vendorLicenseNumberTextBox.Clear();
            categoryDropdown.Text = "";
            vendorCreditCardNumberTextBox.Clear();
            vendorCreditCardTypeDropdown.Text = "";
            festivalComboBox.Enabled = false;
        }

        private void enableFields(bool maybe)
        {
            vendorCreditCardNumberTextBox.Enabled = maybe;
            vendorCreditCardTypeDropdown.Enabled = maybe;
            vendorLicenseNumberTextBox.Enabled = maybe;
            vendorNameTextBox.Enabled = maybe;
            vendorTelephoneNumberTextBox.Enabled = maybe;
            categoryDropdown.Enabled = maybe;
        }

        private void submitButton_Click(object sender, EventArgs e)
        {
            if (addVendorRadioButton.Checked)
            {
                addVendor();
            }
            else if (editVendorRadioButton.Checked)
            {
                editVendorInfo();
            }
            else
            {
                removeVendor();
            }
        }

        private void festivalComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            addVendorButton.Enabled = true;
        }

        private void addVendorButton_Click(object sender, EventArgs e)
        {
            addVendorButton.Enabled = false;

            SQLiteCommand nonQuery = dbAccess.getCommand();
            nonQuery.CommandText = "INSERT INTO festival_has_vendor " +
                                   "VALUES (@festID, @licNum);";
            SQLiteParameter param = new SQLiteParameter("@festID", getFestivalNumber(festivalComboBox.SelectedItem.ToString()));
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@licNum", oldLicNum);
            nonQuery.Parameters.Add(param);

            dbAccess.executeNonQuery(nonQuery);
            festivalComboBox.Text = "";
            updateFestivalListAndComboBox();
        }

        private void festivalAttendingListBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            removeVendorButton.Enabled = true;
        }

        private void removeVendorButton_Click(object sender, EventArgs e)
        {
            SQLiteCommand nonQuery = dbAccess.getCommand();
            nonQuery.CommandText = "DELETE FROM festival_has_vendor " +
                                   "WHERE festival_id=@festID AND vendor_license_num=@licNum;";
            SQLiteParameter param = new SQLiteParameter("@festID", getFestivalNumber(festivalAttendingListBox.SelectedItem.ToString()));
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@licNum", oldLicNum);
            nonQuery.Parameters.Add(param);

            dbAccess.executeNonQuery(nonQuery);
            updateFestivalListAndComboBox();
            removeVendorButton.Enabled = false;
        }
    }
}
